﻿#region "Imports"
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using Practico2.DataTypes;

#endregion

namespace Practico2.DataAccess
{

    public class UsuarioDataAccess
    {

        public static int InsertUsuario(DTUsuario toInsert)
        {
            int toReturn = 0;
            SqlTransaction transaction = TransactionManager.GetTransaction();
            SqlConnection connection = null;
            SqlDataReader reader = null;
            string insertCommandText = "INSERT INTO Usuario([NombreUsuario],[ContraseniaUsuario]) VALUES (@NombreUsuario,@ContraseniaUsuario) ; SELECT @@Identity";
            bool useTransaction = (transaction != null);
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlCommand command;


            if (useTransaction)
            {
                command = new SqlCommand(insertCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(insertCommandText, connection);
            }

            if (toInsert.IsNombreUsuarioNull)
            {
                command.Parameters.Add(new SqlParameter("@NombreUsuario", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@NombreUsuario", toInsert.NombreUsuario));
            }
            if (toInsert.IsContraseniaUsuarioNull)
            {
                command.Parameters.Add(new SqlParameter("@ContraseniaUsuario", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@ContraseniaUsuario", toInsert.ContraseniaUsuario));
            }

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }

                reader = command.ExecuteReader();
                reader.Read();
                toReturn = Convert.ToInt32(reader.GetValue(0));
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }

                if ((connection != null) && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            return toReturn;
        }

        public static void UpdateUsuario(DTUsuario toUpdate)
        {
            SqlTransaction transaction = TransactionManager.GetTransaction();
            SqlConnection connection = null;
            bool useTransaction = (transaction != null);
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }
            string updateCommandText = "UPDATE Usuario SET [NombreUsuario]=@NombreUsuario,[ContraseniaUsuario]=@ContraseniaUsuario WHERE [IdUsuario]=@IdUsuario";

            SqlCommand command;
            if (useTransaction)
            {
                command = new SqlCommand(updateCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(updateCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdUsuario", toUpdate.IdUsuario));

            if (toUpdate.IsNombreUsuarioNull)
            {
                command.Parameters.Add(new SqlParameter("@NombreUsuario", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@NombreUsuario", toUpdate.NombreUsuario));
            }
            if (toUpdate.IsContraseniaUsuarioNull)
            {
                command.Parameters.Add(new SqlParameter("@ContraseniaUsuario", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@ContraseniaUsuario", toUpdate.ContraseniaUsuario));
            }


            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                command.ExecuteNonQuery();

            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }

        }

        public static void DeleteUsuario(Int32 idUsuario)
        {

            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            string deleteCommandText = "DELETE FROM Usuario WHERE [IdUsuario]=@IdUsuario";

            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(deleteCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(deleteCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdUsuario", idUsuario));



            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }

        }

        public static DTUsuario GetUsuario(Int32 idUsuario)
        {

            string selectCommandText = String.Format("SELECT {0} FROM [Usuario] WHERE [IdUsuario]=@IdUsuario", GetUsuarioFields());
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdUsuario", idUsuario));


            DTUsuario current = null;

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    current = GetUsuarioInfo(reader, 0);
                }

                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return current;
        }

        public static List<DTUsuario> ListUsuario()
        {

            List<DTUsuario> toReturn = new List<DTUsuario>();
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }
            string selectCommandText = String.Format("SELECT {0} FROM [Usuario]", GetUsuarioFields());
            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DTUsuario current = GetUsuarioInfo(reader, 0);
                    toReturn.Add(current);
                }
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return toReturn;
        }

        public static string GetUsuarioFields()
        {
            return " [Usuario].[IdUsuario],[Usuario].[NombreUsuario],[Usuario].[ContraseniaUsuario] ";
        }

        public static DTUsuario GetUsuarioInfo(SqlDataReader reader, int index)
        {
            DTUsuario current = new DTUsuario();
            object temp;

            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.IdUsuario = (Int32)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.NombreUsuario = (String)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.ContraseniaUsuario = (String)temp;
            }
            index++;


            return current;
        }

        /// <summary>
        /// Valida si las credenciales del usuario son validas
        /// La contrasenia del usuario debe estar encriptada en la base
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static DTUsuario CredencialesValidas(string username, string password)
        {
            string selectCommandText = String.Format("SELECT {0} FROM [Usuario] WHERE [NombreUsuario]=@NombreUsuario AND [ContraseniaUsuario]=@ContraseniaUsuario ", GetUsuarioFields());
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString"].ConnectionString);
            }

            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@NombreUsuario", username));
            command.Parameters.Add(new SqlParameter("@ContraseniaUsuario", password));


            DTUsuario current = null;

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    current = GetUsuarioInfo(reader, 0);
                }

                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return current;
        }
    }
}